﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;
using Lamex.Base;
using DevExpress.XtraCharts;
using Lamex.Interface;
using System.IO;

namespace Lamex.mDashboard
{
    public partial class frmDB_Desk_Defect_Report : frmBaseChild
    {
        string strSql;
        DataSet ds;

        public frmDB_Desk_Defect_Report()
        {
            InitializeComponent();
        }

        private void frmDB_Desk_Defect_Report_Resize(object sender, EventArgs e)
        {
            splitContainerControl1.SplitterPosition = this.Width / 3;
            splitContainerControl2.SplitterPosition = this.Width / 3;
            splitContainerControl3.SplitterPosition = (this.Height - panelControl1.Height) / 2;
            splitContainerControl4.SplitterPosition = (this.Height - panelControl1.Height) / 2;
            splitContainerControl5.SplitterPosition = (this.Height - panelControl1.Height) / 2;
        }

        private void frmDB_Desk_Defect_Report_Load(object sender, EventArgs e)
        {
			try { base.InitButtons(); } catch { }//初始化标准功能，因要应用于RibbonForm，所以加入异常处理

			//dateEdit1只显示年
			cmb_SelectYear.Properties.CalendarView = DevExpress.XtraEditors.Repository.CalendarView.Vista;
            cmb_SelectYear.Properties.ShowToday = false;
            cmb_SelectYear.Properties.ShowMonthHeaders = false;
            cmb_SelectYear.Properties.VistaCalendarInitialViewStyle = DevExpress.XtraEditors.VistaCalendarInitialViewStyle.YearsGroupView;
            cmb_SelectYear.Properties.VistaCalendarViewStyle = DevExpress.XtraEditors.VistaCalendarViewStyle.YearsGroupView;
            cmb_SelectYear.Properties.Mask.EditMask = "yyyy";
            cmb_SelectYear.Properties.Mask.UseMaskAsDisplayFormat = true;
        }

        private void sbtnView_Click(object sender, EventArgs e)
        {
           if(string.IsNullOrEmpty(cmb_SelectYear.Text))
            {
                XtraMessageBox.Show("年份不能为空！");
                return;
            }
            List<string> lst = new List<string>();
            lst.Add(cmb_SelectYear.Text);
            string sql = @"declare @startDate nvarchar(10);
	declare @endDate nvarchar(10);
	set @startDate='{0}'+'0101'
	set @endDate='{1}'+'1201'
    --year summary
	SELECT 'MES' as MES,  N'工厂' AS 报废类型,年份,COUNT(PART_BARCODE) AS [报废数量(pcs)],CONVERT(decimal(10,2) ,SUM(SCRAPVOLUME)) AS [报废体积 (m³)] into #t
	FROM
	(SELECT DATENAME(yyyy,UPDATE_TS) AS 年份,PART_BARCODE,SCRAPVOLUME
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS]
	WHERE PART_STATE != '212') AS a 
	where DATENAME(yyyy,a.年份)='{2}'
	GROUP BY 年份
	UNION
	SELECT 'MES' as MES, N'供应商' AS 报废类型,年份,COUNT(PART_BARCODE) AS [报废数量(pcs)],CONVERT(decimal(10,2) ,SUM(SCRAPVOLUME)) AS [报废体积 (m³)]
	FROM
	(SELECT DATENAME(yyyy,UPDATE_TS) AS 年份,PART_BARCODE,SCRAPVOLUME
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS]
	WHERE PART_STATE = '212') AS a where  
	DATENAME(yyyy,a.年份)='{3}'
	GROUP BY 年份

	select  n.报废类型,n.[报废数量(pcs)], CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal,k.[报废数量和(pcs)])=0.0 then 0.0 else  CONVERT(decimal,n.[报废数量(pcs)])/CONVERT(decimal,k.[报废数量和(pcs)]) end *100.0))+'%' as [报废数量(%)],
	n.[报废体积 (m³)], CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal(18,4),k.[报废体积和(m³)])=0.0 then 0.0 else  CONVERT(decimal(18,4),n.[报废体积 (m³)])/CONVERT(decimal(18,4),k.[报废体积和(m³)]) end *100.0))+'%' as [报废体积(%)] from #t n left join (
	select 'MES' as MES, SUM(CONVERT(decimal, m.[报废数量(pcs)])) as [报废数量和(pcs)],SUM(m.[报废体积 (m³)]) as [报废体积和(m³)] from #t m) k
	on n.MES=k.MES
	union all
	select 'Total' as 报废类型,SUM(m.[报废数量(pcs)]),'100%'  as [报废数量(%)] ,SUM(m.[报废体积 (m³)]),'100%'  as [报废体积 (%)]   from #t m
	--month pcs Qty summary
	SELECT 
	  'Total'   as 行标签  ,COUNT(PART_BARCODE) AS [补料数量(pcs)工厂],SUM(isnull(SCRAPVOLUME,0)) AS [报废体积 (m³)工厂] , 0 as [CNMonth] into #t1
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS] 
	WHERE PART_STATE != '212'  and DATENAME(yyyy,UPDATE_TS)='{4}'
	group by DATENAME(yyyy,UPDATE_TS)
	union all
	select n.ENMonth as 行标签,isnull(m.[补料数量(pcs)工厂],0) as [补料数量(pcs)工厂],isnull(m.[报废体积 (m³)工厂],0) as [报废体积 (m³)工厂] ,n.CNMonth from [Edgeband_Program].[dbo].[Desk_ENMonth] n
	 left join (
SELECT 
	 MONTH(UPDATE_TS) as 行标签 ,COUNT(PART_BARCODE) AS [补料数量(pcs)工厂],SUM(SCRAPVOLUME) AS [报废体积 (m³)工厂]
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS]
	WHERE PART_STATE != '212' and DATENAME(yyyy,UPDATE_TS)='{5}'
	group by MONTH(UPDATE_TS) 
	 ) m on m.行标签=n.CNMonth


	 SELECT 
	  'Total'   as 行标签  ,COUNT(PART_BARCODE) AS [补料数量(pcs)供应商],SUM(isnull(SCRAPVOLUME,0)) AS [报废体积 (m³)供应商] , 0 as [CNMonth] into #t2
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS] 
	WHERE PART_STATE = '212'  and DATENAME(yyyy,UPDATE_TS)='{6}'
	group by DATENAME(yyyy,UPDATE_TS)
	union all
	select n.ENMonth as 行标签,isnull(m.[补料数量(pcs)供应商],0) as [补料数量(pcs)供应商],isnull(m.[报废体积 (m³)供应商],0) as [报废体积 (m³)供应商]  ,n.CNMonth from [Edgeband_Program].[dbo].[Desk_ENMonth] n
	 left join (
SELECT 
	 MONTH(UPDATE_TS) as 行标签 ,COUNT(PART_BARCODE) AS [补料数量(pcs)供应商],SUM(SCRAPVOLUME) AS [报废体积 (m³)供应商]
	FROM [Edgeband_Program].[dbo].[VIEW_DEFECT_STATUS]
	WHERE PART_STATE = '212' and DATENAME(yyyy,UPDATE_TS)='{7}'
	group by MONTH(UPDATE_TS) 
	 ) m on m.行标签=n.CNMonth

	
	 --factory accumulate
	  select a.行标签,a.[补料数量(pcs)工厂],CONVERT(decimal(10,2) ,a.[报废体积 (m³)工厂]) as  [报废体积 (m³)工厂]  ,
	CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal,a.[补料数量(pcs)工厂])!=0 then  CONVERT(decimal,a.[补料数量(pcs)工厂])/(CONVERT(decimal,a.[补料数量(pcs)工厂])+CONVERT(decimal,b.[补料数量(pcs)供应商]))*100.0 else 0 end))+'%' as [补料数量(%)],
	CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal(18,4),a.[报废体积 (m³)工厂])!=0 then  CONVERT(decimal(18,4),a.[报废体积 (m³)工厂])/(CONVERT(decimal(18,4),a.[报废体积 (m³)工厂])+CONVERT(decimal(18,4),b.[报废体积 (m³)供应商]))*100.0 else 0 end))+'%' as [补料体积 (%)]
	  from #t1 a
	 left join #t2 b on b.行标签=a.行标签 order by a.CNMonth

	 --supplier
	 select a.行标签,a.[补料数量(pcs)供应商],CONVERT(decimal(10,2) ,a.[报废体积 (m³)供应商]) [报废体积 (m³)供应商],
	CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal,a.[补料数量(pcs)供应商])!=0 then CONVERT(decimal,a.[补料数量(pcs)供应商])/(CONVERT(decimal,b.[补料数量(pcs)工厂])+CONVERT(decimal,a.[补料数量(pcs)供应商]))*100.0 else 0 end))+'%' as [补料数量(%)],
	CONVERT(nvarchar, CONVERT(decimal(10,2), case when CONVERT(decimal(18,4),a.[报废体积 (m³)供应商])!=0 then CONVERT(decimal(18,4),a.[报废体积 (m³)供应商])/(CONVERT(decimal(18,4),b.[报废体积 (m³)工厂])+CONVERT(decimal(18,4),a.[报废体积 (m³)供应商]))*100.0 else 0 end))+'%' as [补料体积 (%)]
	  from #t2 a
	 left join #t1 b on b.行标签=a.行标签 order by a.CNMonth

	 drop table #t
	 drop table #t1
	 drop table #t2";
		    sql = string.Format(sql, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text, cmb_SelectYear.Text);
			DataSet ds = Lamex.Base.BaseBuildSql.GetDataSetBySql(sql);
           // ds =   Lamex.Base.BaseBuildSql.GetProNameDataSet("Edgeband_Program.dbo.p_Desk_DefectMaterialYearSummary", lst);

            if (ds.Tables.Count > 0)
            {
                gridControl1.DataSource = ds.Tables[0];
                gridView1.OptionsBehavior.Editable = false;
                gridView1.OptionsView.ColumnAutoWidth = true;
				

                gridControl2.DataSource = ds.Tables[1];
                gridView2.OptionsBehavior.Editable = false;
                gridView2.OptionsView.ColumnAutoWidth = true;

                gridControl3.DataSource = ds.Tables[2];
                gridView3.OptionsBehavior.Editable = false;
                gridView3.OptionsView.ColumnAutoWidth = true;


				//init chart
				chartControl1.Series.Clear();
				chartControl2.Series.Clear();
				chartControl3.Series.Clear();
				DataTable dt = new DataTable();
				DataTable dt1 = new DataTable();
				DataTable dt2 = new DataTable();
				dt = ds.Tables[0].Copy();
				if(dt.Rows.Count>2)
					dt.Rows.RemoveAt(2);
				dt1 = ds.Tables[1].Copy();
				if (dt1.Rows.Count != 0)
					dt1.Rows.RemoveAt(0);
				dt2 = ds.Tables[2].Copy();
				if (dt2.Rows.Count != 0)
					dt2.Rows.RemoveAt(0);

				Series seriesAuto = new Series("", ViewType.Bar);
				seriesAuto.DataSource = dt;
				seriesAuto.ArgumentDataMember = "报废类型";
				seriesAuto.ValueDataMembers.AddRange(new string[] { "报废数量(pcs)" });
				seriesAuto.Label.Font = new System.Drawing.Font("Tahoma", 8F);
				seriesAuto.PointOptions.PointView = PointView.Argument;
				seriesAuto.PointOptions.ValueNumericOptions.Format = NumericFormat.Number;
				chartControl1.Series.Add(seriesAuto);
                //series title positive setting
                Legend Legends = (Legend)chartControl1.Legend;
                Legends.Visibility = DevExpress.Utils.DefaultBoolean.True;
                Legends.AlignmentVertical = LegendAlignmentVertical.TopOutside;
                Legends.AlignmentHorizontal = LegendAlignmentHorizontal.Left;
                Legends.Direction = LegendDirection.LeftToRight;
                Legends.TextVisible = true;

				Series seriesAutoQty = new Series("补料数量(工厂)", ViewType.Bar);
				seriesAutoQty.DataSource = dt1;
				seriesAutoQty.ArgumentDataMember = "行标签";
				seriesAutoQty.ValueDataMembers.AddRange(new string[] { "补料数量(pcs)工厂" });
				seriesAutoQty.Label.Font = new System.Drawing.Font("Tahoma", 8F);
				seriesAutoQty.PointOptions.PointView = PointView.Argument;
				seriesAutoQty.PointOptions.ValueNumericOptions.Format = NumericFormat.Number;
				chartControl2.Series.Add(seriesAutoQty);

				Series _seriesAutoQty = new Series("补料数量(供应商)", ViewType.Bar);
				_seriesAutoQty.DataSource = dt2;
				_seriesAutoQty.ArgumentDataMember = "行标签";
				_seriesAutoQty.ValueDataMembers.AddRange(new string[] { "补料数量(pcs)供应商" });
				_seriesAutoQty.Label.Font = new System.Drawing.Font("Tahoma", 8F);
				_seriesAutoQty.PointOptions.PointView = PointView.Argument;
				_seriesAutoQty.PointOptions.ValueNumericOptions.Format = NumericFormat.Number;
				chartControl2.Series.Add(_seriesAutoQty);

				//series title positive setting
				Legend LegendsMonth = (Legend)chartControl2.Legend;
				LegendsMonth.Visibility = DevExpress.Utils.DefaultBoolean.True;
				LegendsMonth.AlignmentVertical = LegendAlignmentVertical.TopOutside;
				LegendsMonth.AlignmentHorizontal = LegendAlignmentHorizontal.Center;
				LegendsMonth.Direction = LegendDirection.LeftToRight;
				LegendsMonth.TextVisible = true;


				Series seriesAutoVoulum = new Series("报废体积 (m³)工厂", ViewType.Bar);
				seriesAutoVoulum.DataSource = dt1;
				seriesAutoVoulum.ArgumentDataMember = "行标签";
				seriesAutoVoulum.ValueDataMembers.AddRange(new string[] { "报废体积 (m³)工厂" });
				seriesAutoVoulum.Label.Font = new System.Drawing.Font("Tahoma", 8F);
				seriesAutoVoulum.PointOptions.PointView = PointView.Argument;
				seriesAutoVoulum.PointOptions.ValueNumericOptions.Format = NumericFormat.Number;
				chartControl3.Series.Add(seriesAutoVoulum);

				Series _seriesAutoVoulum = new Series("报废体积 (m³)供应商", ViewType.Bar);
				_seriesAutoVoulum.DataSource = dt2;
				_seriesAutoVoulum.ArgumentDataMember = "行标签";
				_seriesAutoVoulum.ValueDataMembers.AddRange(new string[] { "报废体积 (m³)供应商" });
				_seriesAutoVoulum.Label.Font = new System.Drawing.Font("Tahoma", 8F);
				_seriesAutoVoulum.PointOptions.PointView = PointView.Argument;
				_seriesAutoVoulum.PointOptions.ValueNumericOptions.Format = NumericFormat.Number;
				chartControl3.Series.Add(_seriesAutoVoulum);

				//series title positive setting
				Legend LegendsVoulum = (Legend)chartControl3.Legend;
				LegendsVoulum.Visibility = DevExpress.Utils.DefaultBoolean.True;
				LegendsVoulum.AlignmentVertical = LegendAlignmentVertical.TopOutside;
				LegendsVoulum.AlignmentHorizontal = LegendAlignmentHorizontal.Center;
				LegendsVoulum.Direction = LegendDirection.LeftToRight;
				LegendsVoulum.TextVisible = true;
			}
        }

        private void gridControl2_Click(object sender, EventArgs e)
        {

        }
		public override void Refresh()
		{
			
			base.Refresh();
		}

		public override void DoView(IButtonInfo sender)
		{
			sbtnView_Click(null, null);
			base.DoView(sender);
		}
		public override void DoExport (IButtonInfo sender)
		{
			////导出
			//using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在导出数据...", new Size(400, 80)))
			//{
			//	string dir = @"D:\DefectReport";
			//	if(!Directory.Exists(dir))
			//	{
			//		Directory.CreateDirectory(dir);
			//	}
				
			//	gridView1.ExportToXlsx(dir+@"\年报"+ DateTime.Now.ToString("yyyyMMdd_HHmmss")+ ".xlsx");
			//	gridView2.ExportToXlsx(dir + @"\月报-工厂" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
			//	gridView3.ExportToXlsx(dir + @"\月报-供应商" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
			//}
			//	base.DoExport(sender);
		}


		private void cm_Subexport1_Click(object sender, EventArgs e)
		{
			string path = @"D:\MESTemp";
			if (!Directory.Exists(path))
			{
				Directory.CreateDirectory(path);
			}
			SaveFileDialog dlg = new SaveFileDialog();
			dlg.Title = "导出Excel";
			dlg.InitialDirectory = path;//导出默认路径
			dlg.Filter = "Excel文件(*.xlsx)|*.xlsx";//导出默认格式
			dlg.FileName = "年报_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");//导出默认名称
			if (dlg.ShowDialog() == DialogResult.OK)
			{
				//view.ExportToPdf(dlg.FileName);
				//view.ExportToXls(dlg.FileName);
				Lamex.Common.ExecelHelper.ExportToExcelHeader(gridView1, dlg.FileName);
				if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
					System.Diagnostics.Process.Start(dlg.FileName);//打开指定路径下的文件
			}
		}
		private void cm_Subexport2_Click(object sender, EventArgs e)
		{
			string path = @"D:\MESTemp";
			if (!Directory.Exists(path))
			{
				Directory.CreateDirectory(path);
			}
			SaveFileDialog dlg = new SaveFileDialog();
			dlg.Title = "导出Excel";
			dlg.InitialDirectory = path;//导出默认路径
			dlg.Filter = "Excel文件(*.xlsx)|*.xlsx";//导出默认格式
			dlg.FileName = "月报-工厂_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");//导出默认名称
			if (dlg.ShowDialog() == DialogResult.OK)
			{
				//view.ExportToPdf(dlg.FileName);
				//view.ExportToXls(dlg.FileName);
				Lamex.Common.ExecelHelper.ExportToExcelHeader(gridView2, dlg.FileName);
				if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
					System.Diagnostics.Process.Start(dlg.FileName);//打开指定路径下的文件
			}
		}

		private void cm_Subexport3_Click(object sender, EventArgs e)
		{
			string path = @"D:\MESTemp";
			if (!Directory.Exists(path))
			{
				Directory.CreateDirectory(path);
			}
			SaveFileDialog dlg = new SaveFileDialog();
			dlg.Title = "导出Excel";
			dlg.InitialDirectory = path;//导出默认路径
			dlg.Filter = "Excel文件(*.xlsx)|*.xlsx";//导出默认格式
			dlg.FileName = "月报-供应商_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");//导出默认名称
			if (dlg.ShowDialog() == DialogResult.OK)
			{
				//view.ExportToPdf(dlg.FileName);
				//view.ExportToXls(dlg.FileName);
				Lamex.Common.ExecelHelper.ExportToExcelHeader(gridView3, dlg.FileName);
				if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
					System.Diagnostics.Process.Start(dlg.FileName);//打开指定路径下的文件
			}
		}

		
	}
}